const { query, connect } = require('../model/index');
const dictModel = require('../model/dict');
const commodityModel = require('../model/commodity');

/**
 * 获取字典
 * @param {*} params 
 * @returns 
 */
const getDictsParse = async (params) => {
  const database = dictModel.TABLE_NAME;
  const dictList = params.dictMap;

  const dictItems = Object.create(null);
  let [rows, fields] = await connect.query(
    `
    select
      a.id,
      a.title,
      b.id as dict_item_id,
      b.title as dict_item_title,
      b.min,
      b.max
    from
      ${database} a
    left join ${database}_item b on
      a.id = b.dict_id
    where
      a.status = ? and b.status = ? and a.name in (?)
    `,
    [1, 1, dictList]
  );
  rows.forEach(item => {
    if (!dictItems[item.id]) {
      dictItems[item.id] = [];
    }
    dictItems[item.id].push(item);
  });

  let dictSql = `select * from ${database} where status = ? and name in (?)`;
  let [dicts] = await connect.query(
    dictSql,
    [1, dictList]
  );
  if (dicts[0]) {
    dicts.forEach(item => {
      item.items = dictItems[item.id];
    });
  }

  return dicts;
};

/**
 * 查询列表
 * @param {*} queryParams 
 * @param {*} param1 
 * @returns 
 */
const commodityList = async (queryParams, { pageNum, pageSize }) => {
  const whereArr = [];

  // 前台筛选价格
  if (queryParams.price) {
    const dictDatabase = dictModel.TABLE_NAME;
    let [dictItem] = await connect.query(`SELECT id,min,max from ${dictDatabase}_item where id = ?`, [queryParams.price]);
    if (dictItem[0]) {
      const min = dictItem[0].min;
      const max = dictItem[0].max;
      whereArr.push(`price >= ${min}`);
      max > 0 && whereArr.push(`price <= ${max}`);
    }
  }

  // 前台筛选粉丝数量
  if (queryParams.fans_num) {
    const dictDatabase = dictModel.TABLE_NAME;
    let [dictItem] = await connect.query(`SELECT id,min,max from ${dictDatabase}_item where id = ?`, [queryParams.fans_num]);
    if (dictItem[0]) {
      const min = dictItem[0].min;
      const max = dictItem[0].max;
      whereArr.push(`fans_num >= ${min}`);
      max > 0 && whereArr.push(`fans_num <= ${max}`);
    }
  }

  if (queryParams.fans_min) {
    whereArr.push(`fans_num >= ${queryParams.fans_min}`);
  }
  if (queryParams.fans_max) {
    whereArr.push(`fans_num <= ${queryParams.fans_max}`);
  }
  if (queryParams.price_min) {
    whereArr.push(`price >= ${queryParams.price_min}`);
  }
  if (queryParams.price_max) {
    whereArr.push(`price <= ${queryParams.price_max}`);
  }

  if (queryParams.startTime) {
    whereArr.push(`create_time >= ${new Date(queryParams.startTime).getTime() / 1000}`);
  }
  if (queryParams.endTime) {
    whereArr.push(`create_time <= ${new Date(queryParams.endTime).getTime() / 1000}`);
  }

  for (let key in queryParams) {
    if (['price', 'status', 'fans_min', 'fans_max', 'fans_num', 'price_min', 'price_max', 'startTime', 'endTime'].includes(key)) {
      continue;
    }
    if (queryParams[key] != '' && queryParams[key] != null) {
      if (key == 'category') {
        whereArr.push(`FIND_IN_SET('${queryParams[key]}',${key})`);
      } else if (key == 'title') {
        whereArr.push(`${key} like '%${queryParams[key]}%'`);
      } else {
        whereArr.push(`${key} = '${queryParams[key]}'`);
      }
    }
  }
  
  let statusVal;
  if (queryParams['status'] && queryParams['status'] instanceof Array) {
    statusVal = queryParams['status'];
  } else {
    statusVal = [1];
  }

  const whereAnd = whereArr.length > 0 ? whereArr.join(' and ') + ' and' : '';

  const database = commodityModel.TABLE_NAME;
  // let [rows, fields] = await query(
  //   `
  //   select 
  //     *,b.realname 
  //   from 
  //     ${database} a left join oa_admin_user b on a.admin_user_id = b.id 
  //   where ${whereAnd} a.status in (?) 
  //   order by
  //     a.id desc limit ? offset ?`,
  //   [statusVal, pageSize, pageNum * pageSize]
  // );
  let [rows, fields] = await connect.query(
    `
    select 
      *
    from 
      ${database} 
    where ${whereAnd} status in (?) 
    order by
      id desc limit ? offset ?`,
    [statusVal, pageSize, pageNum * pageSize]
  );
  let [countRows] = await connect.query(
    `select count(*) as count from ${database} where ${whereAnd} status in (?)`,
    [statusVal]
  );
  const total = countRows[0].count || 0;
  const pageCount = Math.ceil(total / pageSize); // 总页数
  
  // console.log(`select * from ${database} where ${whereAnd} status in (1, 0) order by id desc limit 10 offset 0`);
  // let [test] = await query(
  //   `select * from ${database} where FIND_IN_SET(?,category) and status in (?) order by id desc`,
  //   [50, [1,0]]
  // );
  // console.log('test',test.length);

//   console.log(`select 
//   *,b.realname 
// from 
//   ${database} a left join oa_admin_user b on a.admin_user_id = b.id 
// where ${whereAnd} a.status in (1) 
// order by
//   a.id desc`);

  console.log(`select 
      *
    from 
      ${database} 
    where ${whereAnd} status in (?) 
    order by
      id desc limit ? offset ?`);

  const adminUserIds = rows.map(item => item.admin_user_id);
  console.log('adminUserIds', adminUserIds);
  if (adminUserIds.length != 0) {
    let adminUserSql = `select id,realname from oa_admin_user where FIND_IN_SET(id,?)`;
    let [adminUser] = await connect.query(
      adminUserSql,
      [...new Set(adminUserIds)].join(',')
    );
    let adminMap = {};
    adminUser.forEach(item => {
      adminMap[item.id] = item.realname;
    });

    rows.forEach(item => {
      item.admin_user_name = adminMap[item.admin_user_id];
    });
  }

  return {
    pageCount,
    list: rows,
    total
  }
}


module.exports = {
  getDictsParse,
  commodityList
}